﻿'Description:排产计划确认（转工、检验、外委）后台方法
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangrj 
'create Date:20220706
'Rewriter:
'Rewrite Date:
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class ApsPlanConfirmBusiness

    ''' <summary>
    ''' 查询排产记录的bom信息
    ''' add:Wangjh 20220707
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanContainerBom(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql = <string>select bb.bomname,b.bomrevision,b.bomid,pb.productname,p.description productdesc,p.productid,pm.qtyrequired
,sb2.specname,s.specrevision,s.specid from bom b
left join bombase bb on bb.bombaseid=b.bombaseid
left join productmateriallistitem pm on pm.bomid=b.bomid
left join product p on p.productid=pm.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join specbase sb on sb.specbaseid=pm.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,pm.specid)
left join specbase sb2 on sb2.specbaseid=s.specbaseid
where 1=1 </string>.Value
        If para.ContainsKey("WorkflowID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowID")) Then
                strSql += String.Format(" and b.workflowid='{0}' ", para("WorkflowID"))
            End If
        End If

        If para.ContainsKey("SpecID") Then
            If Not String.IsNullOrWhiteSpace(para("SpecID")) Then
                strSql += String.Format(" and s.SpecID='{0}' ", para("SpecID"))
            End If
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取工装信息
    ''' add:Wangjh 20220707
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetApsPlanTools(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql = <string>select wt.workflowstepid,ct.cuttoolname toolname,ct.description tooldesc,wt.qty qtyrequired, '刀具' tooltype from workflowsteptools wt
inner join cuttool ct on ct.cuttoolid=wt.tools--刀具
where 1=1 {0}
union all
select wt.workflowstepid,mt.measuretoolname toolname,mt.description tooldesc,wt.qty,'量具' tooltype from workflowsteptools wt
inner join MeasureTool mt on mt.measuretoolid=wt.tools--量具表
where 1=1 {0}
union all
select wt.workflowstepid,st.specialtoolname toolname,st.description tooldesc,wt.qty,'夹模具' tooltype from workflowsteptools wt
inner join SpecialTool st on st.specialtoolid=wt.tools--夹模具表
where 1=1 {0}</string>.Value

        Dim condition As String = ""
        If para.ContainsKey("WorkflowStepID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepID")) Then
                condition += " and wt.workflowstepid='" + para("WorkflowStepID") + "' "
            End If
        End If

        strSql = String.Format(strSql, condition)

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 获取排产确认列表数据（分页）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanDatasByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim strSql As String = <string>
SELECT Row_Number() OVER( ORDER BY cci.mfgorderid,cci.containerid,ws.sequence) numer,
apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,p.productid,p.description,
pb.productname,cci.containertype,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,
cci.originalfactoryid,api.workflowid,s.specid,
DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,
apd.starttime,apd.endtime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排产','') apstypename,
apd.createdate,apd.specfactoryid,f.factoryname AS assisFactoryName,fc.factoryname AS mainFactoryName,
Decode(NVL(apd.status,0),0,'未确认',1,'下发',2,'已确认') AS statusName,NVL(apd.status,0) as status,apd.FEEDBACKDATE,
apd.confirmdate,cci.qty,apd.qty apsqty
FROM apsplandetail apd
LEFT JOIN apsplaninfo api ON api.apsplaninfoid = apd.apsplaninfoid
LEFT JOIN containercurrentinfo cci ON cci.containerid = apd.containerid
LEFT JOIN factory fc ON fc.factoryid = cci.originalfactoryid
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid 
LEFT JOIN factory f ON f.factoryid = apd.specfactoryid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = apd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
</string>.Value

            If para("Type") = "1" Then '转工 (弃用，已单独实现)
                strSql += " where cci.originalfactoryid <> apd.specfactoryid "
            ElseIf para("Type") = "2" Then '检验
                strSql += " where s.isspotcheck = 1 "
            ElseIf para("Type") = "3" Then '外委
                strSql += " where ws.controlcode = 'LY05'"
            ElseIf para("Type") = "4" Then 'BOM
                strSql += <string>
                             left join (SELECT DISTINCT b.workflowid,s1.specid FROM bom b 
                                        left join productmateriallistitem pm on pm.bomid=b.bomid
                                        left join specbase sb1 on sb1.specbaseid=pm.specbaseid
                                        left join spec s1 on s1.specid=nvl(sb1.revofrcdid,pm.specid))k
                             on k.workflowid = api.workflowid AND k.specid = s.specid
                             where k.specid is not null
                          </string>.Value
            ElseIf para("Type") = "5" Then '工装
                strSql += " where EXISTS (SELECT 1 FROM workflowsteptools wt WHERE wt.workflowstepid = apd.workflowstepid)"
            Else '未知
                result.Result = False
                result.Message = "未知功能"
                Return result
                Exit Function
            End If

            strSql += <string> and api.isuse = 0 AND apd.isuse = 0 AND api.apstype2=2
                        AND cci.isinpool=1 AND cci.containerstatus=1 
                      </string>.Value

            If para.ContainsKey("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName").ToString) Then
                    strSql += String.Format(" AND cci.mfgordername = '{0}'", para("ScanOrderName").ToString)
                End If
            End If
            If para.ContainsKey("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName").ToString) Then
                    strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If
            If para.ContainsKey("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName").ToString) Then
                    strSql += String.Format(" AND cci.containername LIKE '%{0}%'", para("ContainerName").ToString)
                End If
            End If
            If para.ContainsKey("AssisFactoryId") Then
                If Not String.IsNullOrWhiteSpace(para("AssisFactoryId").ToString) Then
                    strSql += String.Format(" AND apd.specfactoryid = '{0}'", para("AssisFactoryId").ToString)
                End If
            End If
            If para.ContainsKey("MainFactoryId") Then
                If Not String.IsNullOrWhiteSpace(para("MainFactoryId").ToString) Then
                    strSql += String.Format(" AND cci.originalfactoryid = '{0}'", para("MainFactoryId").ToString)
                End If
            End If
            If para.ContainsKey("WorkCenterId") Then
                If Not String.IsNullOrWhiteSpace(para("WorkCenterId").ToString) Then
                    strSql += String.Format(" AND apd.workcenterid = '{0}'", para("WorkCenterId").ToString)
                End If
            End If
            If para.ContainsKey("Status") Then
                If Not String.IsNullOrWhiteSpace(para("Status").ToString) Then
                    strSql += String.Format(" AND nvl(apd.status,0) = '{0}'", para("Status").ToString)
                Else
                    strSql += " AND nvl(apd.status,0) in ('0','2')"
                End If
            End If
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString()
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存排产计划确认（通用确认）
    ''' </summary>
    ''' <param name="employeeId"></param>
    ''' <param name="dtDatas"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveApsPlanConfirmDatas(ByVal employeeId As String, ByVal dtDatas As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim strq = "select apd.apsplandetailid from apsplandetail apd where nvl(apd.status,'0') <> '0' and ("
            Dim strsql As New StringBuilder
            Dim sqlList As New ArrayList
            For Each dr As DataRow In dtDatas.Rows
                strq += String.Format(" apd.apsplandetailid = '{0}' or", dr("apsPlanDetailId"))
                strsql.Clear()
                strsql.AppendFormat("UPDATE apsplandetail ap SET ")
                strsql.AppendFormat("ap.confirmempid = '{0}',", employeeId)
                If dtDatas.Columns.Contains("feedBackDate") Then
                    If Not String.IsNullOrWhiteSpace(dr("feedBackDate").ToString) Then '反馈时间不为空，则添加
                        strsql.AppendFormat("ap.feedbackdate = to_date('{0}','yyyy-MM-dd'),", dr("feedBackDate"))
                    End If
                End If
                strsql.AppendFormat("ap.confirmdate = {0},ap.status = '2'", "sysdate")
                strsql.AppendFormat(" WHERE ap.apsplandetailid = '{0}'", dr("apsPlanDetailId"))
                sqlList.Add(strsql.ToString)
            Next
            strq = strq.Trim("r").Trim("o") + ")"
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strq.ToString)
            If dt.Rows.Count = 0 Then
                DatabaseHelper.ExecuteSqlTran(sqlList)
                result.Result = True
                result.Message = "确认成功"
            Else
                result.Result = False
                result.Message = "数据过期，请重新查询"
            End If
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

#Region "转工排产计划确认"
    ''' <summary>
    ''' 获取排产确认列表数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanShipOutDatasByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim strSql As New StringBuilder
            strSql.AppendFormat(<string>
            SELECT Row_Number() OVER( ORDER BY cci.mfgorderid,cci.containerid,ws.sequence) numer,
            apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,p.productid,p.description,
            pb.productname,cci.containertype,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,cci.workflowstepname nowworkflowstepname,
            cci.originalfactoryid,api.workflowid,s.specid,apd.shiprecplanstartdate,apd.shiprecplanenddate,wb.workflowname||':'||w.workflowrevision workflowinfo,
            DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,
            apd.starttime,apd.endtime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排产','') apstypename,
            apd.createdate,apd.specfactoryid,f.factoryname AS assisFactoryName,fc.factoryname AS mainFactoryName,s.specno,
            Decode(NVL(apd.status,0),0,'未确认',1,'下发',2,'已确认',3,'已接收') AS statusName,NVL(apd.status,0) as status,apd.FEEDBACKDATE,
            apd.confirmdate,cci.qty,apd.qty apsqty,apd.shipreceivedate,e.fullname confirmemp,em.fullname receiveemp,tm.teamname
            FROM apsplandetail apd
            LEFT JOIN employee e ON e.employeeid = apd.confirmempid
            LEFT JOIN employee em ON em.employeeid = apd.shipreceiveempid
            LEFT JOIN apsplaninfo api ON api.apsplaninfoid = apd.apsplaninfoid
            LEFT JOIN containercurrentinfo cci ON cci.containerid = apd.containerid
            LEFT JOIN factory fc ON fc.factoryid = cci.originalfactoryid
            LEFT JOIN product p ON p.productid = cci.productid
            LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid 
            LEFT JOIN factory f ON f.factoryid = apd.specfactoryid
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = apd.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN Workflow w ON w.workflowid = api.workflowid
            LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
            LEFT JOIN productteaminfo pti ON pti.productname = cci.productname AND pti.isuse = 0
            LEFT JOIN team tm ON tm.teamid = pti.teamid
            where cci.originalfactoryid = '{0}' AND apd.specfactoryid != '{0}' and api.isuse = 0 AND apd.isuse = 0 AND api.apstype2=2
            AND cci.isinpool=1 AND cci.containerstatus=1 AND cci.isinstore != 1 AND NVL(NVL(ws.controlcode,s.controlcode),'0') != 'LY05'</string>.Value, para("MainFactoryId"))

            If para.ContainsKey("scanOrder") Then
                If Not String.IsNullOrWhiteSpace(para("scanOrder")) Then
                    strSql.AppendFormat(" AND cci.mfgordername = '{0}'", para("scanOrder"))
                End If
            End If
            If para.ContainsKey("scanContainer") Then
                If Not String.IsNullOrWhiteSpace(para("scanContainer")) Then
                    strSql.AppendFormat(" AND ((cci.containertype <> '7' and cci.containername = '{0}')", para("scanContainer"))
                    strSql.AppendFormat(" or (cci.containertype = '7' and cci.togetherflag = '{0}'))", para("scanContainer"))
                End If
            End If
            If para.ContainsKey("productName") Then
                If Not String.IsNullOrWhiteSpace(para("productName")) Then
                    strSql.AppendFormat(" AND pb.productname LIKE '%{0}%'", para("productName"))
                End If
            End If
            If para.ContainsKey("containerName") Then
                If Not String.IsNullOrWhiteSpace(para("containerName")) Then
                    strSql.AppendFormat(" AND cci.containername LIKE '%{0}%'", para("containerName"))
                End If
            End If
            If para.ContainsKey("specNo") Then
                If Not String.IsNullOrWhiteSpace(para("specNo")) Then
                    strSql.AppendFormat(" AND ws.workflowstepname like '%{0}%'", para("specNo"))
                End If
            End If
            If para.ContainsKey("assistFactory") Then
                If Not String.IsNullOrWhiteSpace(para("assistFactory")) Then
                    strSql.AppendFormat(" AND apd.specfactoryid = '{0}'", para("assistFactory"))
                End If
            End If
            If para.ContainsKey("workCenter") Then
                If Not String.IsNullOrWhiteSpace(para("workCenter")) Then
                    strSql.AppendFormat(" AND apd.workcenterid = '{0}'", para("workCenter"))
                End If
            End If

            If para.ContainsKey("apsType") Then
                If Not String.IsNullOrWhiteSpace(para("apsType")) Then
                    strSql.AppendFormat(" AND api.apsType = '{0}'", para("apsType"))
                End If
            End If

            If Not String.IsNullOrWhiteSpace(para("planStartDate")) Then
                strSql.AppendFormat(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("planStartDate"))
            End If

            If Not String.IsNullOrWhiteSpace(para("planEndDate")) Then
                strSql.AppendFormat(" AND apd.starttime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("planEndDate"))
            End If

            If Not String.IsNullOrWhiteSpace(para("shipStartDate")) Then
                strSql.AppendFormat(" AND apd.shiprecplanstartdate >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("shipStartDate"))
            End If

            If Not String.IsNullOrWhiteSpace(para("shipEndDate")) Then
                strSql.AppendFormat(" AND apd.shiprecplanstartdate <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("shipEndDate"))
            End If

            If para("status") = "2" Then '是否反馈只会在确认状态下
                If para("isFeedback") = "1" Then
                    strSql.AppendLine(" AND apd.shiprecplanstartdate IS NOT NULL")
                Else
                    strSql.AppendLine(" AND apd.shiprecplanstartdate IS NULL")
                End If
                strSql.AppendLine(" AND (nvl(apd.status,0) = '2' OR nvl(apd.status,0) = '4')")
            ElseIf para("status") = "0" Then
                strSql.AppendLine(" AND ws.sequence >= DECODE(cci.Isinstore, -1, 0, cci.workflowstepsequence)")
                strSql.AppendLine(<string> AND NOT EXISTS (SELECT 1 FROM apsplandetail ap 
                            left JOIN apsplaninfo ai ON ai.apsplaninfoid = ap.apsplaninfoid
                            WHERE ap.apsplandetailid != apd.apsplandetailid AND ap.workflowstepid = ws.workflowstepid 
                            AND ai.containerid = cci.containerid AND ap.isuse = 0 AND ai.isuse = 0 AND ap.status != '0')</string>.Value)
                strSql.AppendLine(" AND nvl(apd.status,0) = 0")
            Else
                strSql.AppendFormat(" AND nvl(apd.status,0) = '{0}'", para("status"))
            End If

            '件号主制单元
            If Not String.IsNullOrWhiteSpace(para("ddlProductTeam")) Then
                strSql.AppendFormat(" AND pti.teamid = '{0}'", para("ddlProductTeam"))
            End If

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("pageNumber"))
            paraPage.PageSize = Convert.ToInt32(para("pageSize"))
            paraPage.strSQL = strSql.ToString()
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 获取转工排产计划接收数据
    ''' </summary>
    ''' <param name="param"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanShipReceivedDatas(ByVal param As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim strsql As New StringBuilder
            strsql.AppendFormat(<string>SELECT Row_Number() OVER( ORDER BY cci.mfgorderid,cci.containerid,ws.sequence) numer,
                                apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid,
                                cci.productdescription,cci.productname,cci.containertype,apd.workflowstepid,apd.workflowstepname,cci.workflowstepname nowworkflowstepname,
                                cci.originalfactoryid,api.workflowid,s.specid,to_char(apd.shiprecplanstartdate,'yyyy-MM-dd hh24:mi:ss') shiprecplanstartdate,
                                to_char(apd.shiprecplanenddate,'yyyy-MM-dd hh24:mi:ss') shiprecplanenddate,
                                DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,
                                apd.starttime,apd.endtime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排产','') apstypename,
                                apd.createdate,apd.specfactoryid,f.factoryname AS assisFactoryName,fc.factoryname AS mainFactoryName,
                                Decode(NVL(apd.status,0),0,'未确认',1,'下发',2,'已确认',3,'已接收') AS statusName,NVL(apd.status,0) as status,
                                apd.confirmdate,cci.qty,apd.qty apsqty,wb.workflowname||':'||w.workflowrevision workflowinfo,
                                apd.shipreceivedate,e.fullname confirmemp,em.fullname receiveemp
                                FROM containercurrentinfo cci
                                LEFT JOIN apsplaninfo api ON api.containerid = cci.containerid
                                LEFT JOIN apsplandetail apd ON apd.apsplaninfoid = api.apsplaninfoid
                                LEFT JOIN employee e ON e.employeeid = apd.confirmempid
                                LEFT JOIN employee em ON em.employeeid = apd.shipreceiveempid
                                LEFT JOIN factory fc ON fc.factoryid = cci.originalfactoryid
                                LEFT JOIN factory f ON f.factoryid = apd.specfactoryid
                                LEFT JOIN Workflowstep ws ON ws.workflowstepid = apd.workflowstepid
                                LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                                LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                                LEFT JOIN Workflow w ON w.workflowid = api.workflowid
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
                                WHERE cci.originalfactoryid != '{0}' AND api.isuse = 0 AND apd.isuse = 0 AND api.apstype2=2
                                AND cci.isinpool=1 AND cci.containerstatus=1 AND apd.specfactoryid = '{0}' AND cci.isinstore != 1 
                                AND NVL(NVL(ws.controlcode,s.controlcode),'0') != 'LY05'</string>.Value, param("FactoryID"))

            If param.ContainsKey("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(param("ScanOrderName")) Then
                    strsql.AppendFormat(" AND cci.mfgordername = '{0}'", param("ScanOrderName"))
                End If
            End If
            If param.ContainsKey("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(param("ScanContainerName")) Then
                    strsql.AppendFormat(" AND ((cci.containertype <> '7' and cci.containername = '{0}')", param("ScanContainerName"))
                    strsql.AppendFormat(" or (cci.containertype = '7' and cci.togetherflag = '{0}'))", param("ScanContainerName"))
                End If
            End If
            If param.ContainsKey("ProductName") Then
                If Not String.IsNullOrWhiteSpace(param("ProductName")) Then
                    strsql.AppendFormat(" AND cci.productname LIKE '%{0}%'", param("ProductName"))
                End If
            End If
            If param.ContainsKey("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(param("ContainerName")) Then
                    strsql.AppendFormat(" AND cci.containername LIKE '%{0}%'", param("ContainerName"))
                End If
            End If
            If param.ContainsKey("MainFactoryId") Then
                If Not String.IsNullOrWhiteSpace(param("MainFactoryId")) Then
                    strsql.AppendFormat(" AND cci.originalfactoryid = '{0}'", param("MainFactoryId"))
                End If
            End If
            If param.ContainsKey("ApsType") Then
                If Not String.IsNullOrWhiteSpace(param("ApsType")) Then
                    strsql.AppendFormat(" AND api.apsType = '{0}'", param("ApsType"))
                End If
            End If

            If param("Status") = "2" Then '未接收
                strsql.AppendLine(" AND (nvl(apd.status,0) = '2' OR nvl(apd.status,0) = '4')")
            Else
                strsql.AppendFormat(" AND nvl(apd.status,0) = '{0}'", param("Status"))
            End If

            If Not String.IsNullOrWhiteSpace(param("PlanStartDate")) Then
                strsql.AppendFormat(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", param("PlanStartDate"))
            End If

            If Not String.IsNullOrWhiteSpace(param("PlanEndDate")) Then
                strsql.AppendFormat(" AND apd.starttime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", param("PlanEndDate"))
            End If
            If param("isPage").Equals("true") Then 'add by longb 2024-07-31
                Dim paraPage As New uMESPagingDataDTO
                paraPage.CurrentPageIndex = Convert.ToInt32(param("PageIndex"))
                paraPage.PageSize = Convert.ToInt32(param("PageSize"))
                paraPage.strSQL = strsql.ToString()
                result = DatabaseHelper.RunProcedureForPage(paraPage)
            Else
                'If param.ContainsKey("limitNum") Then '限制条数
                '    result.DBTable = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT * FROM ({0}) WHERE ROWNUM <= {1}", strsql.ToString(), param("limitNum")))
                'Else
                '    result.DBTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
                'End If
                result.DBTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
            End If
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

#End Region

#Region "排产反馈"
    ''' <summary>
    ''' 获取排产反馈数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsFeedBackByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim strSql As String = <string>
SELECT apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,p.productid,p.description,
pb.productname,cci.containertype,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,
wb.workflowname,w.workflowid,apd.confirmdate,apd.confirmempid,e.fullname,wm_concat(e1.employeeid) employeeids,
DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,
apd.starttime,apd.endtime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排产','') apstypename,
Decode(NVL(apd.status,0),0,'未确认',1,'下发',2,'确认') AS statusName,NVL(apd.status,0) as status,apd.FEEDBACKDATE,cci.qty,
apd.workcenterid,apd.teamid,apd.resourceid,s.specid,WS.MAKEEMPCOUNTS,WS.PREPARECYCLE,WS.MAKECYCLE,WS.TRANSFERCYCLE,WS.SHIPCYCLE,WS.COMMICYCLE,
ws.sequence
FROM apsplandetail apd
LEFT JOIN apsplaninfo api ON api.apsplaninfoid = apd.apsplaninfoid
LEFT JOIN containercurrentinfo cci ON cci.containerid = apd.containerid
LEFT JOIN Workflow w ON w.workflowid = cci.workflowid
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid 
LEFT JOIN employee e ON e.employeeid = apd.confirmempid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = apd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid) 
left join ApsPlanDetailEmp ae on ae.apsplandetailid=apd.apsplandetailid
left join employee e1 on e1.employeeid=ae.employeeid
WHERE api.isuse = 0 AND apd.isuse = 0 
AND cci.isinpool=1 AND cci.containerstatus=1 AND api.apstype2=2
AND apd.feedbackdate IS NOT NULL and cci.originalfactoryid = '{0}'
</string>.Value

            strSql = String.Format(strSql, para("factory").ToString) '添加分厂筛选

            If para.ContainsKey("mfgOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("mfgOrderName").ToString) Then
                    strSql += String.Format(" AND cci.mfgordername LIKE '%{0}%'", para("mfgOrderName").ToString)
                End If
            End If
            If para.ContainsKey("containerName") Then
                If Not String.IsNullOrWhiteSpace(para("containerName").ToString) Then
                    strSql += String.Format(" AND cci.containername LIKE '%{0}%'", para("containerName").ToString)
                End If
            End If
            If para.ContainsKey("productName") Then
                If Not String.IsNullOrWhiteSpace(para("productName").ToString) Then
                    strSql += String.Format(" AND pb.productname LIKE '%{0}%'", para("productName").ToString)
                End If
            End If
            If para.ContainsKey("startTime") Then
                If Not String.IsNullOrWhiteSpace(para("startTime").ToString) Then
                    strSql += String.Format(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd')", para("startTime").ToString)
                End If
            End If
            If para.ContainsKey("endTime") Then
                If Not String.IsNullOrWhiteSpace(para("endTime").ToString) Then
                    strSql += String.Format(" AND apd.endtime <= to_date('{0}','yyyy-MM-dd')", para("endTime").ToString)
                End If
            End If
            strSql += <string>
                          GROUP BY apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,p.productid,p.description,
pb.productname,cci.containertype,cci.workflowroute,cci.productname,
wb.workflowname,w.workflowid,apd.confirmdate,apd.confirmempid,e.fullname,
apd.workflowstepid,apd.workflowstepname,WS.MAKEEMPCOUNTS,WS.PREPARECYCLE,WS.MAKECYCLE,WS.TRANSFERCYCLE,WS.SHIPCYCLE,WS.COMMICYCLE,
apd.starttime,apd.endtime,api.apstype,cci.containerno,ws.sequence,
apd.FEEDBACKDATE,cci.qty,cci.containertype,apd.status,
apd.workcenterid,apd.teamid,apd.resourceid,s.specid
                      </string>.Value
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString()
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

End Class
